import java.io.File;
import java.io.IOException;

import java.sql.*;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;



public class RegionXML2MySQL {

	/**
	 * @param args
	 * @throws ParserConfigurationException 
	 * @throws IOException 
	 * @throws SAXException 
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public static void main(String[] args) throws ParserConfigurationException, IOException, SAXException, ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
		//!!! set character_set_results='utf8';set names 'utf8'; !!! in mysql client
		Class.forName("com.mysql.jdbc.Driver");  
        String url = "jdbc:mysql://localhost/test?user=root&password=123456&useUnicode=true&characterEncoding=UTF-8";  
        Connection conn = (Connection) DriverManager.getConnection(url);  
        Statement stmt = null;
        stmt = conn.createStatement();
        try{
        	stmt.execute("drop table Counties");
        	stmt.execute("drop table Cities");
        	stmt.execute("drop table Provinces");
        }catch(Exception e){
        	e.printStackTrace();
        }
        String sql = "create table Provinces("
        		+ "code int not null,"
        		+ "name varchar(50) not null,"
        		+ "primary key (code)"
        		+") type=MyISAM";

       stmt.execute(sql);
        sql = "create table Cities("
        		+ "code int not null,"
        		+ "name varchar(50) not null,"
        		+ "p_code int not null,"
        		+ "primary key (code),"
        		+ "constraint cp_l foreign key(p_code) references Provinces(code)"
        		+") type=MyISAM";
        stmt.execute(sql);
        sql = "create table Counties("
        		+ "code int not null,"
        		+ "name varchar(50) not null,"
        		+ "c_code int not null,"
        		+ "primary key (code),"
        		+ "constraint cc_l foreign key(c_code) references Cities(code)"
        		+") type=MyISAM";
        stmt.execute(sql);
		
		String filename = "GBT2260-1999.xml";
		File f=new File(filename);
		DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
		DocumentBuilder builder=factory.newDocumentBuilder();
		Document doc = builder.parse(f);
		Element country = (Element)doc.getElementsByTagName("country").item(0);
		//System.out.print("车牌号码:" + doc.getElementsByTagName("NO").item(i).getFirstChild().getNodeValue());
		int cnt = 0;
		NodeList pro = country.getElementsByTagName("province");
		for(int i=0;i<pro.getLength();i++){
			Element pe = (Element)pro.item(i); 
			String pcode = pe.getAttribute("code");
			System.out.println(pcode+" "+pe.getAttribute("name"));
			sql = "insert into Provinces(code,name) VALUES ("+pcode+",'"+pe.getAttribute("name")+"')";
			System.out.println(sql);
			try{
			stmt.execute(sql);
			}catch(SQLException e){
				System.err.println("ERROR:"+e.getMessage());
			}
			NodeList city = pe.getElementsByTagName("city");
			//System.out.println("CITY "+city.getLength());
			for(int j=0;j<city.getLength();j++){
				Element ce = (Element)city.item(j); 
				String ccode = ce.getAttribute("code");
				//System.out.println(ccode+" "+pe.getAttribute("name"));
				sql = "insert into Cities(code,name, p_code) VALUES ("+ccode+",'"+ce.getAttribute("name")+"',"+pcode+")";
				//System.out.println(sql);
				try{
				    stmt.execute(sql);
				}catch(SQLException e){
					System.err.println("ERROR:"+e.getMessage());
				}
				
				NodeList cy = ce.getElementsByTagName("county");
				//System.out.println("COUNTY: "+cy.getLength());
				for(int k=0;k<cy.getLength();k++){
					Element cce = (Element)cy.item(k); 
					String cccode = cce.getAttribute("code");
					//System.out.println(cccode+" "+cce.getAttribute("name"));
					sql = "insert into Counties(code,name, c_code) VALUES ("+cccode+",'"+cce.getAttribute("name")+"',"+ccode+")";
					cnt++;
					System.out.println("##########"+sql);
					try{
					    stmt.execute(sql);
					}catch(SQLException e){
						System.err.println("ERROR:"+e.getMessage());
					}
				}
				
				
			}
		}
		System.out.println(cnt);
		
		conn.close();
	}

}
